/*
 * 作者: 侯晓宇
 * 时间: 2017-01-11 
 * 说明：
 * Copyright (c) 2016-2017 中研沃兮
 */
define(function(require){
	require('jquery');
	require('md5');
	
	var c = require('app/utils/CommonUtils');
	var appInfo = require('app/AppInfo');
	var app_name = appInfo.name;
	var app_id = appInfo.appid;
	
	var singleton_db = null;
	var db_name = "woxiapp";
	
	var debug_needReset = false;
	
	var getSingleton_db=function(){
		if(singleton_db == null){
			singleton_db =  api.require('db');
		}
		return singleton_db;
	};
	
	return {
	
		getSysInfo:function(){
		  
			var sql = "SELECT db_version, loginuserid, lastlogintime, appname FROM sysinfo;";
			sysinfo_ret = this.db_query(sql);
	        return sysinfo_ret.data[0];
		},
	
		kvset:function(key,value){
			
			var db = getSingleton_db();
			var sql = 'SELECT [key], value FROM keyvalue where key = \''+key+'\'';
			
			var kv_ret = this.db_query(sql);
	        if(c.isNull(value)){
	        	value = "";
	        }
	        var sql_set = "";
	        if(kv_ret.data.length == 0){//没有该key-value,插入
	        	
	        	sql_set = 
	        			" INSERT INTO keyvalue ("+
	                         "key,"+
	                         "value"+
	                     ")"+
	                     " VALUES ("+
	                         "\'"+key+"\',"+
	                         "\'"+value+"\'"+
	                     ")";
	        }else { // 存在该key，更新value
	        	sql_set = 
	        			' update keyvalue ' + 
						' set value = \''+value+'\' '+
						' where key = \''+key+'\'';
	        }

	        var ret = this.db_exec(sql_set);
	        return ret.status;
		},
		
		kvget:function(key){
			var db = getSingleton_db();
			var sql = 'SELECT [key], value FROM keyvalue where key = \''+key+'\'';
			var kv_ret = this.db_query(sql);
			if(kv_ret.data.length !=0 ){
	        	return kv_ret.data[0].value;
	        }else{
	        	return null;
	        }
	    },
		db_query:function(sql){
			c.log("db_query_sql:"+sql);
			
			var db = getSingleton_db();
			
			var ret = db.selectSqlSync({
				name:db_name,
				sql:sql
			});
			c.log("db_query_result:"+JSON.stringify(ret));
			return ret;
		},
		db_exec:function(sql){
			c.log("db_exec_sql:"+sql);
			var db = getSingleton_db();
			var ret = db.executeSqlSync({
				name:db_name,
				sql:sql
			});
			c.log("db_exec_result:"+JSON.stringify(ret));
			return ret;
		},
		initSqlliteDB:function(){
		
			var db = getSingleton_db();
			
			var ret = db.openDatabaseSync({
			    name: db_name
			});
			
			//Key-Value数据库，退出登陆时进行数据清空
			var sql1 = 'CREATE TABLE keyvalue ("key" VARCHAR (50), value VARCHAR (500))';
			var sql2 = 'SELECT * FROM sqlite_master where type="table" and name = "keyvalue"';
			var sql3 = 'drop table keyvalue';

			var ret_exist = this.db_query(sql2);
			if(ret_exist.data.length ==0 || debug_needReset){ // 没有创建过，， 创建
				this.db_exec(sql3);
				this.db_exec(sql1);
			}
			
			
			//imagecache网络图片缓存数据库
			var sql4 = 'CREATE TABLE imagecache ("key" VARCHAR (50), "remoteurl" VARCHAR (1000), "cacheurl" VARCHAR (1000))';
			var sql5 = 'SELECT * FROM sqlite_master where type="table" and name = "imagecache"';
			var sql6 = 'drop table imagecache';
			var ret_exist = this.db_query(sql5);
			if(ret_exist.data.length ==0 || debug_needReset){ // 没有创建过，， 创建
				this.db_exec(sql6);
				this.db_exec(sql4);
			}
			
			
		},
		saveImageCache: function(remote_url, cache_url){
			var key = $.md5(remote_url);
			
			var db = getSingleton_db();
			var sql = 'SELECT [key], remoteurl, cacheurl FROM imagecache where key = \''+key+'\'';
			
			var ret = this.db_query(sql);

	        var sql_set = "";
	        if(ret.data.length == 0){//没有该key-value,插入
	        	
	        	sql_set = 
	        			"INSERT INTO imagecache ("+
	                         "key,"+
	                         "remoteurl,"+
	                         "cacheurl"+
	                     ")"+
	                     "VALUES ("+
	                         "\'"+key+"\',"+
	                         "\'"+remote_url+"\',"+
	                         "\'"+cache_url+"\'"+
	                     ")";
	        }else { // 存在该key，更新value
	        	sql_set = 
	        			'update imagecache ' + 
						'set '+
						'remoteurl = "'+remote_url+'" , '+
						'cacheurl = "'+cache_url+'" '+
						'where key = "'+key+'"';
	        }

	        var ret = this.db_exec(sql_set);
	        return ret.status;
		},
		getImageCache: function(remote_url){
			var key = $.md5(remote_url);
			var sql = 'SELECT [key], remoteurl, cacheurl FROM imagecache where key = \''+key+'\'';
			var ret = this.db_query(sql);
			if(ret.data.length !=0 ){
				var imageCacheItem = ret.data[0];
				//key,remoteurl, cacheurl
	        	return imageCacheItem.cacheurl;
	        }else{
	        	return null;
	        }
		},
		resetImageCache: function(){
			//imagecache网络图片缓存数据库
			var sql4 = 'CREATE TABLE imagecache ("key" VARCHAR (50), "remoteurl" VARCHAR (1000), "cacheurl" VARCHAR (1000))';
			var sql6 = 'drop table imagecache';
			this.db_exec(sql6);
			this.db_exec(sql4);
		},
		removeImageCache: function(remote_url){
			var key = $.md5(remote_url);
			var sql6 = 'delete from imagecache where key = \''+key+'\''
			this.db_exec(sql6);
		}
		
		
	};
});